

****created by Mirya in May 2023

***** Dataset creation and replication data for Governing the Gilded Age City: Local Institution Building in the United States ***
***** Mirya R Holman, Lakshmi Iyer, and Christina Wolbretch *****


*** data construction do file 


**** set your base here to construct the data **** 

global Base "/Users/liyer/Dropbox/boards_united_we/data/replications/jhpe2025"
*global Base "C:\Users\mrholman\MH Dropbox\Mirya Holman\boards_united_we/data/replications/jhpe2025"
global Data_basic "$Base/1 datasets"
global Data_clean "$Base/2 cleaned"

clear 

cd "$Data"

set scheme lean2



***********************************************************************************************
***appending together the board level data for LA, Boston, Denver, Chicago
***********************************************************************************************

***BOSTON BOARDS
clear

use "$Data_basic/BOS_personyear_nov2022.dta", clear
**new line Jan 2024
drop if gender_final==. //these are blank names or "--"

replace board = "franklin foundation" if board == "managers of the franklin fund"
replace board = "health commissioners" if board == "health commissioner"
replace board = "hospital trustees" if board == "hospital trustee"
replace board = "park commissioners" if board == "park and rec commissioners"
replace board = "overseers of the public welfare" if board == "public welfare"
replace board = "register of deeds" if board == "register of deeds (second)"
replace board = "sealer of weighs and means" if board == "sealer of weighs and measures"
replace board = "sinking funds commissioners" if board == "sinking funds commission"
replace board = "street commissioners" if board == "street commission"
replace board = "traffic commissioners" if board == "traffic commission"

ren year officeyear
egen tag=tag(officeyear board)
replace boardfem_narrow=99 if boardfem_narrow==.
***number of seats on board
egen nseat = count(board), by(officeyear board)
drop if nseat == 1
***fraction of women on board
egen pwom = mean(gender), by(officeyear board)
***does board have any women
egen anywom = max(gender), by(officeyear board)


*keep if tag==1
gen city="Boston"
tempfile BOSboard
save "`BOSboard'"

***DENVER BOARDS
use "$Data_basic/DEN_personyear.dta", clear
egen tag=tag(officeyear board)

ren gender gender_final
drop if gender_final==. //these are blank names

***number of seats on board
egen nseat = count(board), by(officeyear board)
***fraction of women on board
egen pwom = mean(gender), by(officeyear board)
***does board have any women
egen anywom = max(gender), by(officeyear board)
replace boardfem_narrow=99 if boardfem_narrow==.

gen city="Denver"
tempfile DENboard
save "`DENboard'"

***CHICAGO BOARDS
use "$Data_basic/CHI_personyear_Jan2024.dta", clear
egen tag=tag(officeyear board)

ren gender gender_final
recode gender .=0 //no missing values here

***number of seats on board
egen nseat = count(board), by(officeyear board)
***fraction of women on board
egen pwom = mean(gender), by(officeyear board)
***does board have any women
egen anywom = max(gender), by(officeyear board)
replace boardfem_narrow=99 if boardfem_narrow==.

*keep if tag==1
gen city="Chicago"
tempfile CHIboard
save "`CHIboard'"

***LOS ANGELES BOARDS
use "$Data_basic/LA_personyear.dta", clear
drop year
drop if elected == 1 //11385 obs deleted
drop election_date_str unnamed0 elected
gen elected = regexm(board, "Elected")
drop if elected == 1 //106 dropped
drop elected 
egen tag=tag(officeyear board)
gen gender_t=gender_final*sh_officeyear
gen nseat_t=1*sh_officeyear

drop if gender_final==. //20 obs, all 1950 or later

***number of seats on board
egen nseat = sum(nseat_t), by(officeyear board)
label var nseat "# seats on board"
***numer of women on board
egen nwom = sum(gender_t), by(officeyear board)
label var nwom "# women on board"
***fraction of women on board
gen pwom = nwom/nseat
label var pwom "share of women on board"
***does board have any women
gen anywom = 1 if pwom>0 & pwom~=.
replace anywom=0 if anywom==. & pwom~=.
label var anywom "Board has at least one woman"


replace boardfem_narrow = 99 if boardfem_narrow==.

*keep if tag==1
gen city="Los Angeles"
tempfile LAboard
save "`LAboard'"
append using "`DENboard'", force
append using "`BOSboard'", force
append using "`CHIboard'", force

save "$Data_clean/allcity_boardyear.dta", replace


***********************************************************************************************
**** cleaning club counts data and interpolating population **** 
***********************************************************************************************

clear 
import excel "$Data_basic/club_counts_Nov2024.xlsx", sheet("Sheet1") firstrow


tab officeyear, summ(bos_total)
tab officeyear, summ(chi_total)
tab officeyear, summ(den_total)
tab officeyear, summ(la_total)


tab officeyear, summ(bos_allwomen)
tab officeyear, summ(chi_allwomen)
tab officeyear, summ(den_allwomen)
tab officeyear, summ(la_allwomen)

tsset officeyear
tsfill 

foreach var of varlist chi_total la_total den_total bos_total bos_womclub den_womclub chi_womclub la_womclub bos_allwomen den_allwomen chi_allwomen la_allwomen chi_pop den_pop la_pop bos_pop {
	ipolate `var' officeyear, gen(`var'2) 
}

tab officeyear, summ(bos_total2)
tab officeyear, summ(chi_total2)
tab officeyear, summ(den_total2)
tab officeyear, summ(la_total2)


tempfile ClubCounts
save "`ClubCounts'"

clear 

use "`ClubCounts'"

keep officeyear bos_total2 bos_allwomen2 bos_pop2 bos_womclub2
ren bos_total2 totalclubs
ren bos_pop2 population
ren bos_womclub2 womclubs
ren bos_allwomen2 womclubleader

gen city_dum = 1
gen city = "Boston"


tempfile ClubBoston
save "`ClubBoston'"

clear

use "`ClubCounts'"

keep officeyear chi_total2 chi_allwomen2 chi_pop2 chi_womclub2
ren chi_total2 totalclubs
ren chi_pop2 population
ren chi_womclub2 womclubs
ren chi_allwomen2 womclubleader
gen city_dum = 2
gen city = "Chicago"

tempfile ClubChicago
save "`ClubChicago'"

clear 

use "`ClubCounts'"

keep officeyear den_total2 den_allwomen2 den_pop2 den_womclub2
ren den_total2 totalclubs
ren den_pop2 population
ren den_womclub2 womclubs
ren den_allwomen2 womclubleader
gen city_dum = 3
gen city = "Denver"

tempfile ClubDenver
save "`ClubDenver'"


clear 
use "`ClubCounts'"
keep officeyear la_total2 la_allwomen2 la_pop2 la_womclub2
gen city_dum = 4
gen city = "Los Angeles"
ren la_total2 totalclubs
ren la_pop2 population
ren la_womclub2 womclubs
ren la_allwomen2 womclubleader

tempfile ClubLA
save "`ClubLA'"


****creating longitudinal data
append using "`ClubDenver'", force
append using "`ClubBoston'", force
append using "`ClubChicago'", force

*** appendix B Data **** 

sum totalclubs womclubs if city_dum == 1
sum totalclubs womclubs if city_dum == 2
sum totalclubs womclubs if city_dum == 3
sum totalclubs womclubs if city_dum == 4

save "$Data_clean/Clubs.dta", replace

***********************************************************************************************
**** cleaning mayoral data
***********************************************************************************************

****BOSTON MAYORS
clear 
import excel "$Data_basic/boston mayors.xlsx", sheet("Sheet1") firstrow
rename Year officeyear 
rename Uncategorized unaffiliated 
rename Machine machine 
rename Progressive progressive 
foreach var of varlist unaffiliated machine progressive {
	recode `var' .=0
}
gen city_dum = 1
gen city = "Boston"
summ progressive machine unaffiliated
sort officeyear city_dum

tempfile MayorBoston
save "`MayorBoston'"

***CHICAGO MAYORS
clear
import excel "$Data_basic/chicago mayors our years.xlsx", sheet("Sheet1") firstrow
rename year officeyear 
rename Mayor mayor
gen city_dum = 2
foreach var of varlist unaffiliated machine progressive {
	recode `var' .=0
}

drop if officeyear>1937
gen city = "Chicago"
summ progressive machine unaffiliated
sort officeyear city_dum

tempfile MayorChicago
save "`MayorChicago'"



****LOS ANGELES MAYORS
clear
import excel "$Data_basic/los angeles mayors our years.xlsx", sheet("Sheet2") firstrow
foreach var of varlist unaffiliated machine progressive {
	recode `var' .=0
}
gen city = "Los Angeles"
gen city_dum = 4
summ progressive machine unaffiliated
sort officeyear city_dum

tempfile MayorLA
save "`MayorLA'"


***DENVER MAYORS
clear
import excel "$Data/denver/denver mayors.xlsx", sheet("Sheet1") firstrow
rename Mayor mayor
rename Year officeyear 
gen city = "Denver"
gen city_dum = 3
foreach var of varlist Neither Machine Progressive {
	recode `var' .=0
}


rename Neither unaffiliated 
rename Machine machine 
rename Progressive progressive 

summ progressive machine unaffiliated
sort officeyear city_dum
drop mayor 

tempfile MayorDenver
save "`MayorDenver'"


append using "`MayorBoston'", force
append using "`MayorLA'", force
append using "`MayorChicago'", force

*** descriptives for Appendix B **** 

tab city unaffiliated
tab city machine
tab city progressive

save "$Data_clean/mayors.dta", replace

clear

***********************************************************************************************
**** cleaning board level data and creating period dummies ***** 
***********************************************************************************************

use "$Data_clean/allcity_boardyear.dta"
gen LAdum = (city=="Los Angeles")
gen DENdum = (city=="Denver")
gen BOSdum = (city=="Boston")
gen CHIdum = (city=="Chicago")

gen city_dum = .
replace city_dum = 4 if city=="Los Angeles" 
replace city_dum = 3 if city=="Denver" 
replace city_dum = 1 if city=="Boston" 
replace city_dum = 2 if city=="Chicago" 

***keeping relevant period
keep if officeyear <=1945 & officeyear>=1880 //39,781 obs deleted


***********************************************************************************************
***********MERGING WITH MAYORS AND CLUBS DATA*******
***********************************************************************************************

*** merging in mayoral data **** 

cap drop _merge 
sort officeyear city_dum
merge m:1  officeyear city_dum using "$Data_clean/mayors.dta" //unmerged is 63 years for which we have mayor data but not boards data
keep if _m==3

***merging in clubs data
drop _merge 
sort city_dum officeyear
merge m:1  city_dum officeyear  using  "$Data_clean/Clubs.dta" //unmerged is 132 years for which we have (interpolated) clubs data but not boards data
drop if _m==2
drop _merge 

cap drop anywoman
gen anywoman = (pwom>0)


*** labeling **** 

label var BOSdum "Boston" 
label var DENdum "Denver" 
label var LAdum "Los Angeles" 
label var CHIdum "Chicago" 

label var pwom "Share of women on board" 
label var anywom "Board has any woman"
label var gender_final "Woman board member"

lab var progressive "Progressive mayor"
lab var machine "Machine mayor"
lab var unaffiliated "Unaffiliated mayor"

gen womorgpc = womclubs*1000/population
lab var womorgpc "# women's organizations per 1000 population"
gen womorgpc_alt = womclubleader*1000/population
lab var womorgpc_alt "# women's organizations (alt) per 1000 population"
gen womorgratio = womclubs/totalclubs
lab var womorgratio "Women's organizations (as a share of total)"
gen womorgratio_alt = womclubleader/totalclubs
lab var womorgratio_alt "Women's organizations, alt (as a share of total)"
gen clubspc = totalclubs*1000 /population
lab var clubspc "Total organizations per 1000 population"

gen pop_scale = population/1000000
label var pop_scale "Million population"

***********************************************************************************************
***Sept 2024: Board categorization created by MH, updated by Lakshmi in Oct 2024
***********************************************************************************************
replace board = lower(board)

gen children = 0
replace children = 1 if (regexm(board, "youth"))
replace children = 1 if (regexm(board, "child")) 
replace children = 1 if (regexm(board, "juvenil"))
replace children = 1 if (regexm(board, "famil"))
replace children = 1 if (regexm(board, "teen"))
replace children = 1 if (regexm(board, "nursery"))
replace children = 1 if (regexm(board, "education"))
replace children = 1 if (regexm(board, "age"))
replace children = 1 if (regexm(board, "school"))
replace children = 1 if (regexm(board, "bully"))
replace children= 1 if (regexm(board, "literacy"))
replace children= 1 if (regexm(board, "science"))
replace children= 1 if (regexm(board, "head start"))
replace children= 1 if (regexm(board, "scholarship"))
replace children= 1 if (regexm(board, "university"))
replace children= 1 if (regexm(board, "school"))
replace children= 1 if (regexm(board, "student"))
replace children= 1 if (regexm(board, "learning"))
replace children= 1 if (regexm(board, "charter")) 
replace children= 1 if (regexm(board, "educat"))
replace children= 1 if (regexm(board, "study"))
replace children= 1 if (regexm(board, "merit"))
replace children= 1 if (regexm(board, "emerging leaders"))
replace children= 1 if (regexm(board, "early learning"))
replace children= 1 if (regexm(board, "orphan"))
replace children= 1 if (regexm(board, "clayton trust"))


gen civil_rights = 0
replace civil_rights = 1 if (regexm(board, "human rights"))
replace civil_rights = 1 if (regexm(board, "prisoner"))
replace civil_rights = 1 if (regexm(board, "police oversight"))
replace civil_rights = 1 if (regexm(board, "police review"))
replace civil_rights = 1 if (regexm(board, "justice reform"))
replace civil_rights = 1 if (regexm(board, "police relation"))
replace civil_rights = 1 if (regexm(board, "civilian review"))
replace civil_rights = 1 if (regexm(board, "incarceration reform"))
replace civil_rights= 1 if (regexm(board, "lgbt"))
replace civil_rights= 1 if (regexm(board, "hispanic"))
replace civil_rights= 1 if (regexm(board, "latino"))
replace civil_rights= 1 if (regexm(board, "native"))
replace civil_rights= 1 if (regexm(board, "african"))
replace civil_rights= 1 if (regexm(board, "black"))
replace civil_rights= 1 if (regexm(board, "human rights"))
replace civil_rights= 1 if (regexm(board, "civil rights"))
replace civil_rights= 1 if (regexm(board, "immigra"))
replace civil_rights= 1 if (regexm(board, "women"))
replace civil_rights= 1 if (regexm(board, "native"))
replace civil_rights= 1 if (regexm(board, "american indian"))
replace civil_rights= 1 if (regexm(board, "martin"))
replace civil_rights= 1 if (regexm(board, "mlk"))
replace civil_rights = 1 if (regexm(board, "martin luther king"))
replace civil_rights = 1 if (regexm(board, "immigrant"))
replace civil_rights = 1 if (regexm(board, "disabilit"))
replace civil_rights= 1 if (regexm(board, "affirmative action"))
replace civil_rights = 1 if (regexm(board, "sweatfree")) 
replace civil_rights = 1 if (regexm(board, "complaints and discipline")) 
replace civil_rights = 1 if (regexm(board, "impairments ")) 
replace civil_rights = 1 if (regexm(board, "equity")) 
replace civil_rights = 1 if (regexm(board, "force review ")) 
replace civil_rights = 1 if (regexm(board, "grievance board")) 
replace civil_rights = 1 if (regexm(board, "independent review")) 
replace civil_rights = 1 if (regexm(board, "fair practices")) 
replace civil_rights = 1 if (regexm(board, "mobility")) 
replace civil_rights = 1 if (regexm(board, "ombudsman")) 
replace civil_rights = 1 if (regexm(board, "special needs")) 
replace civil_rights = 1 if (regexm(board, "multicultural")) 
 




gen civil_service= 0
replace civil_service= 1 if (regexm(board, "human "))
replace civil_service= 1 if (regexm(board, "appointment"))
replace civil_service= 1 if (regexm(board, "membership"))
replace civil_service= 1 if (regexm(board, "index"))
replace civil_service= 1 if (regexm(board, "employee"))
replace civil_service= 1 if (regexm(board, "ballot"))
replace civil_service= 1 if (regexm(board, "legislat"))
replace civil_service= 1 if (regexm(board, "elections"))
replace civil_service= 1 if (regexm(board, "civic"))
replace civil_service= 1 if (regexm(board, "public servic"))
replace civil_service= 1 if (regexm(board, "pension"))
replace civil_service= 1 if (regexm(board, "civil servic"))
replace civil_service= 1 if (regexm(board, "government"))
replace civil_service= 1 if (regexm(board, "labor relations"))
replace civil_service= 1 if (regexm(board, "personnel"))
replace civil_service= 1 if (regexm(board, "labor"))
replace civil_service= 1 if (regexm(board, "testing and recruiting"))
replace civil_service= 1 if (regexm(board, "salary"))
replace civil_service= 1 if (regexm(board, "arbitration"))
replace civil_service= 1 if (regexm(board, "efficiency"))
replace civil_service= 1 if (regexm(board, "retirement")) 


gen code_regulation = 0  
replace code_regulation = 1 if (regexm(board, "utility"))
replace code_regulation = 1 if (regexm(board, "beet"))
replace code_regulation = 1 if (regexm(board, "mining"))
replace code_regulation = 1 if (regexm(board, "irrigation"))
replace code_regulation = 1 if (regexm(board, "manucturers"))
replace code_regulation = 1 if (regexm(board, "merchants"))
replace code_regulation = 1 if (regexm(board, "meteorology"))
replace code_regulation = 1 if (regexm(board, "meterology"))
replace code_regulation = 1 if (regexm(board, "salesmanship"))
replace code_regulation = 1 if (regexm(board, "vessels"))
replace code_regulation = 1 if (regexm(board, "lodging"))
replace code_regulation = 1 if (regexm(board, "stock exchange"))
replace code_regulation = 1 if (regexm(board, "ways and means"))
replace code_regulation = 1 if (regexm(board, "mines"))
replace code_regulation = 1 if (regexm(board, "licensing"))
replace code_regulation = 1 if (regexm(board, "advertis"))
replace code_regulation = 1 if (regexm(board, "retail"))
replace code_regulation = 1 if (regexm(board, "trade"))
replace code_regulation = 1 if (regexm(board, "manufact"))
replace code_regulation = 1 if (regexm(board, "salesmen"))
replace code_regulation = 1 if (regexm(board, "newspaper"))
replace code_regulation = 1 if (regexm(board, "coal"))
replace code_regulation = 1 if (regexm(board, "packing"))
replace code_regulation = 1 if (regexm(board, "wool"))
replace code_regulation = 1 if (regexm(board, "lodge hous"))
replace code_regulation = 1 if (regexm(board, "stone"))
replace code_regulation = 1 if (regexm(board, "statistics"))
replace code_regulation = 1 if (regexm(board, "industries"))
replace code_regulation = 1 if (regexm(board, "licencing"))
replace code_regulation = 1 if (regexm(board, "bimetallism"))
replace code_regulation = 1 if (regexm(board, "license"))
replace code_regulation = 1 if (regexm(board, "alcohol"))
replace code_regulation = 1 if (regexm(board, "examiners"))
replace code_regulation = 1 if (regexm(board, "automotive"))
replace code_regulation = 1 if (regexm(board, "contractor"))
replace code_regulation = 1 if (regexm(board, "code"))
replace code_regulation = 1 if (regexm(board, "electrical"))
replace code_regulation = 1 if (regexm(board, "plumbing"))
replace code_regulation = 1 if (regexm(board, "engineer"))
replace code_regulation = 1 if (regexm(board, "facilities"))
replace code_regulation = 1 if (regexm(board, "building"))
replace code_regulation = 1 if (regexm(board, "regulatory"))
replace code_regulation = 1 if (regexm(board, "adjustment"))
replace code_regulation = 1 if (regexm(board, "contractor"))
replace code_regulation = 1 if (regexm(board, "redistrict"))
replace code_regulation = 1 if (regexm(board, "control"))
replace code_regulation = 1 if (regexm(board, "water"))
replace code_regulation = 1 if (regexm(board, "electrical"))
replace code_regulation = 1 if (regexm(board, "permit"))
replace code_regulation = 1 if (regexm(board, "cosmetology"))
replace code_regulation = 1 if (regexm(board, "helicopter"))
replace code_regulation = 1 if (regexm(board, "imag"))
replace code_regulation = 1 if (regexm(board, "single room occupancy"))
replace code_regulation = 1 if (regexm(board, "plan "))
replace code_regulation = 1 if (regexm(board, "impact fee"))
replace code_regulation = 1 if (regexm(board, "cemetary"))
replace code_regulation = 1 if (regexm(board, "cemetery"))
replace code_regulation = 1 if (regexm(board, "mechanic"))
replace code_regulation = 1 if (regexm(board, "guide"))
replace code_regulation = 1 if (regexm(board, "appeal"))
replace code_regulation = 1 if (regexm(board, "standards"))
replace code_regulation = 1 if (regexm(board, "administrative hearing "))
replace code_regulation = 1 if (regexm(board, "alarm review"))
replace code_regulation = 1 if (regexm(board, "in rem review board"))
replace code_regulation = 1 if (regexm(board, "property standard"))
replace code_regulation = 1 if (regexm(board, "architectural review"))
replace code_regulation = 1 if (regexm(board, "boiler"))
replace code_regulation = 1 if (regexm(board, "automotive"))
replace code_regulation = 1 if (regexm(board, "public accountancy"))
replace code_regulation = 1 if (regexm(board, "statute"))
replace code_regulation = 1 if (regexm(board, "chamber"))
replace code_regulation = 1 if (regexm(board, "quality"))
replace code_regulation = 1 if (regexm(board, "house"))

 
gen culture = 0 
replace culture= 1 if (regexm(board, "lectures"))
replace culture= 1 if (regexm(board, "heritage"))
replace culture= 1 if (regexm(board, "archeological"))
replace culture= 1 if (regexm(board, " art"))
replace culture= 1 if (regexm(board, "art "))
replace culture= 1 if (regexm(board, "library"))
replace culture= 1 if (regexm(board, "librari"))
replace culture= 1 if (regexm(board, "literacy"))
replace culture= 1 if (regexm(board, "millennial"))
replace culture= 1 if (regexm(board, "landmark"))
replace culture= 1 if (regexm(board, "entertainment"))
replace culture= 1 if (regexm(board, "film"))
replace culture= 1 if (regexm(board, "preservation"))
replace culture= 1 if (regexm(board, "graffitti"))
replace culture= 1 if (regexm(board, "music"))
replace culture= 1 if (regexm(board, "museum"))
replace culture= 1 if (regexm(board, "international"))
replace culture= 1 if (regexm(board, "events"))
replace culture= 1 if (regexm(board, "sister cit"))
replace culture= 1 if (regexm(board, "design"))
replace culture= 1 if (regexm(board, "jazz"))
replace culture= 1 if (regexm(board, "honor"))
replace culture= 1 if (regexm(board, "brewery"))
replace culture= 1 if (regexm(board, "indian"))
replace culture= 1 if (regexm(board, "theater"))
replace culture= 1 if (regexm(board, "symphony"))
replace culture= 1 if (regexm(board, "latino"))
replace culture= 1 if (regexm(board, "golf"))
replace culture= 1 if (regexm(board, "celebrate"))
replace culture= 1 if (regexm(board, "barber"))
replace culture= 1 if (regexm(board, "renaissance"))
replace culture= 1 if (regexm(board, "urban"))
replace culture= 1 if (regexm(board, "gardens"))
replace culture= 1 if (regexm(board, "architec"))
replace culture= 1 if (regexm(board, "gardens"))
replace culture= 1 if (regexm(board, "cultur"))
replace culture= 1 if (regexm(board, "boxing"))
replace culture= 1 if (regexm(board, "fathers"))
replace culture= 1 if (regexm(board, "asia"))
replace culture= 1 if (regexm(board, "ethnic"))
replace culture= 1 if (regexm(board, "gay"))
replace culture= 1 if (regexm(board, "faith"))
replace culture= 1 if (regexm(board, "baseball"))
replace culture= 1 if (regexm(board, "tv"))
replace culture= 1 if (regexm(board, "television"))
replace culture= 1 if (regexm(board, "bike"))
replace culture= 1 if (regexm(board, "fashion"))
replace culture= 1 if (regexm(board, "indicators"))
replace culture= 1 if (regexm(board, "celebration"))
replace culture= 1 if (regexm(board, "sister"))
replace culture= 1 if (regexm(board, "theatre"))
replace culture= 1 if (regexm(board, "fourth of july"))
replace culture= 1 if (regexm(board, "fiesta"))
replace culture= 1 if (regexm(board, "poet laureate "))
replace culture= 1 if (regexm(board, "interna ti onal"))
replace culture= 1 if (regexm(board, "livable "))
replace culture= 1 if (regexm(board, "foundation"))
replace culture= 1 if (regexm(board, "mccune collection"))
replace culture= 1 if (regexm(board, "garden"))
replace culture= 1 if (regexm(board, "puente pride"))
replace culture= 1 if (regexm(board, "team baton rouge"))
replace culture= 1 if (regexm(board, "tourism"))
replace culture= 1 if (regexm(board, "blue ribbon"))
replace culture= 1 if (regexm(board, "award"))
replace culture= 1 if (regexm(board, "picnic"))
replace culture= 1 if (regexm(board, "symphony"))
 
gen econ_development = 0 
replace econ_development= 1 if (regexm(board, "development"))
replace econ_development= 1 if (regexm(board, "annexation"))
replace econ_development= 1 if (regexm(board, "hotel"))
replace econ_development= 1 if (regexm(board, "district"))
replace econ_development= 1 if (regexm(board, "tourist"))
replace econ_development= 1 if (regexm(board, "improvement"))
replace econ_development= 1 if (regexm(board, "economic"))
replace econ_development= 1 if (regexm(board, "investment"))
replace econ_development= 1 if (regexm(board, "oversight"))
replace econ_development= 1 if (regexm(board, "business"))
replace econ_development= 1 if (regexm(board, "commerce"))
replace econ_development= 1 if (regexm(board, "convention"))
replace econ_development= 1 if (regexm(board, "stadium"))
replace econ_development= 1 if (regexm(board, "industrial"))
replace econ_development= 1 if (regexm(board, "international"))
replace econ_development= 1 if (regexm(board, "riverfront"))
replace econ_development= 1 if (regexm(board, "stimulus"))
replace econ_development= 1 if (regexm(board, "incentive"))
replace econ_development= 1 if (regexm(board, "bank"))
replace econ_development= 1 if (regexm(board, "workforce"))
replace econ_development= 1 if (regexm(board, "management"))
replace econ_development= 1 if (regexm(board, "zone"))
replace econ_development= 1 if (regexm(board, "zoni"))
replace econ_development= 1 if (regexm(board, "cannabis"))
replace econ_development= 1 if (regexm(board, "apprenticeship"))
replace econ_development= 1 if (regexm(board, "real estate"))
replace econ_development= 1 if (regexm(board, "downtown"))
replace econ_development= 1 if (regexm(board, "employ"))
replace econ_development= 1 if (regexm(board, "visit"))
replace econ_development= 1 if (regexm(board, "inc"))
replace econ_development= 1 if (regexm(board, "energy"))
replace econ_development= 1 if (regexm(board, "job"))
replace econ_development= 1 if (regexm(board, "electric"))
replace econ_development= 1 if (regexm(board, "invest"))
replace econ_development= 1 if (regexm(board, "revenue"))
replace econ_development= 1 if (regexm(board, "service area"))
replace econ_development= 1 if (regexm(board, "tech"))
replace econ_development= 1 if (regexm(board, "visit"))
replace econ_development= 1 if (regexm(board, "appearance"))
replace econ_development= 1 if (regexm(board, "choose chicago"))
replace econ_development= 1 if (regexm(board, "houston first"))
replace econ_development= 1 if (regexm(board, "media"))
replace econ_development= 1 if (regexm(board, "fund for philadelphia"))
replace econ_development= 1 if (regexm(board, "tif "))
replace econ_development= 1 if (regexm(board, "metropolitan"))
replace econ_development= 1 if (regexm(board, "work"))
replace econ_development= 1 if (regexm(board, "arena"))
replace econ_development= 1 if (regexm(board, "goal"))
replace econ_development= 1 if (regexm(board, "coliseum"))
replace econ_development= 1 if (regexm(board, "beautif"))
replace econ_development= 1 if (regexm(board, "franchises"))
replace econ_development= 1 if (regexm(board, "tourism"))
replace econ_development= 1 if (regexm(board, "corporat"))
replace econ_development= 1 if (regexm(board, "innovation"))
replace econ_development= 1 if (regexm(board, "industrial develop"))
replace econ_development= 0 if (regexm(board, "sport"))
replace econ_development= 1 if (regexm(board, "stadium"))
replace econ_development= 1 if (regexm(board, "indianapolis progress"))
replace econ_development= 1 if (regexm(board, "high performance"))
replace econ_development= 1 if (regexm(board, "convention"))
 

gen environment = 0
replace environment= 1 if (regexm(board, "enviro"))
replace environment= 1 if (regexm(board, "animal"))
replace environment= 1 if (regexm(board, "humane"))
replace environment= 1 if (regexm(board, "forest"))
replace environment= 1 if (regexm(board, "water"))
replace environment= 1 if (regexm(board, "coastal"))
replace environment= 1 if (regexm(board, "bayou"))
replace environment= 1 if (regexm(board, " air "))
replace environment= 1 if (regexm(board, "fish"))
replace environment= 1 if (regexm(board, "creek"))
replace environment= 1 if (regexm(board, "sea"))
replace environment= 1 if (regexm(board, "wetlands"))
replace environment= 1 if (regexm(board, "tree"))
replace environment= 1 if (regexm(board, "conservation"))
replace environment= 1 if (regexm(board, "lake"))
replace environment= 1 if (regexm(board, "weather"))
replace environment= 1 if (regexm(board, "brownfield"))
replace environment= 1 if (regexm(board, "agriculture"))
replace environment= 1 if (regexm(board, "climate"))
replace environment= 1 if (regexm(board, "green"))
replace environment= 1 if (regexm(board, "river"))
replace environment= 1 if (regexm(board, "reuse"))
replace environment= 1 if (regexm(board, "agricult"))
replace environment= 1 if (regexm(board, "conserv"))
replace environment= 1 if (regexm(board, "oil"))
replace environment= 1 if (regexm(board, "tree"))
replace environment= 1 if (regexm(board, "natural resource"))
replace environment = 1 if (regexm(board, "atchafalaya"))
replace environment = 1 if (regexm(board, "extension"))
replace environment = 1 if (regexm(board, "sustainab"))
replace environment = 1 if (regexm(board, "sonoran preserve"))
replace environment = 1 if (regexm(board, "mosquito"))
replace environment = 1 if (regexm(board, "open space"))
replace environment = 1 if (regexm(board, "recycling"))
replace environment = 1 if (regexm(board, "lagoon"))
replace environment = 1 if (regexm(board, "sustainability"))
replace environment = 1 if (regexm(board, "aha moku"))
replace environment = 1 if (regexm(board, "wildlife"))
replace environment = 1 if (regexm(board, "bayou"))
replace environment = 1 if (regexm(board, "clean city"))
replace environment = 1 if (regexm(board, "groundwater"))
 


gen elections = 0 
replace elections= 1 if (regexm(board, "ethics"))
replace elections= 1 if (regexm(board, "mayor"))
replace elections= 1 if (regexm(board, "city council"))
replace elections= 1 if (regexm(board, "election"))
replace elections= 1 if (regexm(board, "vot"))
replace elections= 1 if (regexm(board, "ethic"))
replace elections= 1 if (regexm(board, "campaign"))
replace elections= 1 if (regexm(board, "polling"))
replace elections= 1 if (regexm(board, "old south"))
 


gen fire_police = 0 
replace fire_police= 1 if (regexm(board, "police"))
replace fire_police= 1 if (regexm(board, "policing"))
replace fire_police= 1 if (regexm(board, "fire"))
replace fire_police= 1 if (regexm(board, "officer"))
replace fire_police= 1 if (regexm(board, "relief"))
replace fire_police= 1 if (regexm(board, "safety"))
replace fire_police= 1 if (regexm(board, "forensic"))
replace fire_police= 1 if (regexm(board, "911"))
replace fire_police= 1 if (regexm(board, "arrest")) 
replace fire_police = 1 if (regexm(board, "gun"))
replace fire_police = 1 if (regexm(board, "security"))
replace fire_police = 1 if (regexm(board, "homicide"))
replace fire_police = 1 if (regexm(board, "corrections"))
replace fire_police = 1 if (regexm(board, "sheriff"))
replace fire_police = 1 if (regexm(board, "neighborhood watch"))
replace fire_police = 1 if (regexm(board, "crime"))
replace fire_police = 1 if (regexm(board, "work release"))
replace fire_police = 1 if (regexm(board, "graffiti"))
replace fire_police = 1 if (regexm(board, "pistol"))
replace fire_police = 1 if (regexm(board, "corrections"))
replace fire_police = 1 if (regexm(board, "crime"))
replace fire_police = 1 if (regexm(board, "forensic"))
replace fire_police = 1 if (regexm(board, "juvenile detention"))
replace fire_police = 1 if (regexm(board, "defense"))
replace fire_police = 1 if (regexm(board, "war"))
replace fire_police = 1 if (regexm(board, "citizens service corps"))


gen health = 0
replace health= 1 if (regexm(board, "health"))
replace health= 1 if (regexm(board, "physician"))
replace health= 1 if (regexm(board, "infirmary"))
replace health= 1 if (regexm(board, "hospital"))
replace health= 1 if (regexm(board, "dentist"))
replace health= 1 if (regexm(board, "dental"))
replace health= 1 if (regexm(board, "medicine"))
replace health= 1 if (regexm(board, "nursing"))
replace health= 1 if (regexm(board, "recovery"))
replace health= 1 if (regexm(board, "in home"))
replace health= 1 if (regexm(board, "disability"))
replace health= 1 if (regexm(board, "therapy"))
replace health= 1 if (regexm(board, "speech"))
replace health= 1 if (regexm(board, "counseling"))
replace health= 1 if (regexm(board, "care"))
replace health= 1 if (regexm(board, "hospital"))
replace health= 1 if (regexm(board, "rehabilitation"))
replace health= 1 if (regexm(board, "healthcare"))
replace health= 1 if (regexm(board, "hiv"))
replace health= 1 if (regexm(board, "food"))
replace health= 1 if (regexm(board, "ambulance"))
replace health= 1 if (regexm(board, "vector"))
replace health= 1 if (regexm(board, "sanitation"))
replace health= 1 if (regexm(board, "EMS"))
replace health= 1 if (regexm(board, "mental"))
replace health= 1 if (regexm(board, "chiropractor"))
replace health= 1 if (regexm(board, "nutrition"))
replace health= 1 if (regexm(board, "optometry"))
replace health= 1 if (regexm(board, "pharm"))
replace health= 1 if (regexm(board, "psych"))
replace health= 1 if (regexm(board, "disorders"))
replace health= 1 if (regexm(board, "ems"))
replace health= 1 if (regexm(board, "health"))
replace health= 1 if (regexm(board, "emergency"))
replace health= 1 if (regexm(board, "smoking"))
replace health= 1 if (regexm(board, "tobacco"))
replace health= 1 if (regexm(board, "metro action"))
replace health= 1 if (regexm(board, "medical"))
replace health= 1 if (regexm(board, "chiro"))
replace health= 1 if (regexm(board, "heath"))
replace health= 1 if (regexm(board, "sanatorium"))
replace health= 1 if (regexm(board, "sanitary")) 




gen infrastructure= 0 
replace infrastructure= 1 if (regexm(board, "parking"))
replace infrastructure= 1 if (regexm(board, "traction"))
replace infrastructure= 1 if (regexm(board, "taxi"))
replace infrastructure= 1 if (regexm(board, "station"))
replace infrastructure= 1 if (regexm(board, "ceort"))
replace infrastructure= 1 if (regexm(board, "gas"))
replace infrastructure= 1 if (regexm(board, "transport"))
replace infrastructure= 1 if (regexm(board, "pedestrian"))
replace infrastructure= 1 if (regexm(board, "rail"))
replace infrastructure= 1 if (regexm(board, "red light"))
replace infrastructure= 1 if (regexm(board, "traffic"))
replace infrastructure= 1 if (regexm(board, "transit"))
replace infrastructure= 1 if (regexm(board, "traffic"))
replace infrastructure= 1 if (regexm(board, "waste"))
replace infrastructure= 1 if (regexm(board, "sewer"))
replace infrastructure= 1 if (regexm(board, "sewage"))
replace infrastructure= 1 if (regexm(board, "sanitation"))
replace infrastructure= 1 if (regexm(board, "cable"))
replace infrastructure= 1 if (regexm(board, "pier"))
replace infrastructure= 1 if (regexm(board, "tarc"))
replace infrastructure= 1 if (regexm(board, "route"))
replace infrastructure= 1 if (regexm(board, "interstate"))
replace infrastructure= 1 if (regexm(board, "infrasture"))
replace infrastructure= 1 if (regexm(board, "bridge"))
replace infrastructure= 1 if (regexm(board, "stakehold"))
replace infrastructure= 1 if (regexm(board, "coordimat"))
replace infrastructure= 1 if (regexm(board, "info"))
replace infrastructure= 1 if (regexm(board, "authority"))
replace infrastructure= 1 if (regexm(board, "waste"))
replace infrastructure= 1 if (regexm(board, "disaster"))
replace infrastructure= 1 if (regexm(board, "flood"))
replace infrastructure= 1 if (regexm(board, "aviation "))
replace infrastructure= 1 if (regexm(board, "airport"))
replace infrastructure= 1 if (regexm(board, "corridors"))
replace infrastructure= 1 if (regexm(board, "citilink"))
replace infrastructure= 1 if (regexm(board, "towing"))
replace infrastructure= 1 if (regexm(board, "undergrounding"))
replace infrastructure= 1 if (regexm(board, "us 36"))
replace infrastructure= 1 if (regexm(board, "infrastructure"))
replace infrastructure= 1 if (regexm(board, "harbor"))

replace infrastructure = 1 if (regexm(board, "port"))
replace infrastructure = 1 if (regexm(board, "transport"))
replace infrastructure= 1 if (regexm(board, "airport"))
replace infrastructure= 1 if (regexm(board, "highway"))
replace infrastructure= 1 if (regexm(board, "pilot"))
replace infrastructure= 1 if (regexm(board, "roads"))
replace infrastructure= 1 if (regexm(board, "airport"))
replace infrastructure= 1 if (regexm(board, "airport"))
  
 
gen judicial= 0 
replace judicial= 1 if (regexm(board, "judge")) 
replace judicial= 1 if (regexm(board, "jury")) 
replace judicial= 1 if (regexm(board, "judicial"))
replace judicial= 1 if (regexm(board, "criminal"))
replace judicial= 1 if (regexm(board, "justice"))
replace judicial= 1 if (regexm(board, "attorney"))
replace judicial= 1 if (regexm(board, "public defender"))
replace judicial= 1 if (regexm(board, "sentencing"))
replace judicial= 1 if (regexm(board, "law"))
replace judicial= 1 if (regexm(board, "legal"))
replace judicial= 1 if (regexm(board, "gang"))
replace judicial= 1 if (regexm(board, "criminal justice"))
replace judicial= 1 if (regexm(board, "defender"))
replace judicial= 1 if (regexm(board, "court"))
replace judicial= 1 if (regexm(board, "censors"))
 
 
 
gen parks_rec = 0
replace parks_rec = 1 if (regexm(board, "park"))
replace parks_rec = 1 if (regexm(board, "bath"))
replace parks_rec = 1 if (regexm(board, "playground"))
replace parks_rec = 1 if (regexm(board, "park"))
replace parks_rec = 1 if (regexm(board, "park"))
replace parks_rec = 1 if (regexm(board, "park"))
replace parks_rec = 1 if (regexm(board, "park"))
replace parks_rec = 1 if (regexm(board, "bicycl"))
replace parks_rec = 1 if (regexm(board, "recreation"))
replace parks_rec = 1 if (regexm(board, "walk"))
replace parks_rec = 1 if (regexm(board, "sport"))
replace parks_rec = 0 if (regexm(board, "transport"))
replace parks_rec = 1 if (regexm(board, "pedestri"))
replace parks_rec = 1 if (regexm(board, "field"))
replace parks_rec = 1 if (regexm(board, "golf"))
replace parks_rec = 1 if (regexm(board, "explorium"))
replace parks_rec = 1 if (regexm(board, "arboretum"))
replace parks_rec = 1 if (regexm(board, "equestrian"))



gen planning = 0
replace planning = 1 if (regexm(board, "zoning"))
replace planning = 1 if (regexm(board, "assessor"))
replace planning = 1 if (regexm(board, "zoning"))
replace planning = 1 if (regexm(board, "deeds"))
replace planning = 1 if (regexm(board, "zoning"))
replace planning = 1 if (regexm(board, "property"))
replace planning = 1 if (regexm(board, "zoning"))
replace planning = 1 if (regexm(board, "land"))
replace planning = 1 if (regexm(board, "assess"))
replace planning = 1 if (regexm(board, "plann"))
replace planning = 1 if (regexm(board, "preservation"))
replace planning = 1 if (regexm(board, "zoning"))
replace planning = 1 if (regexm(board, "historic"))
replace planning = 1 if (regexm(board, "city plan"))
replace planning = 1 if (regexm(board, "construction"))
replace planning = 1 if (regexm(board, "design review"))
replace planning = 1 if (regexm(board, "redevelop"))
replace planning = 1 if (regexm(board, "board of review"))
replace planning = 1 if (regexm(board, "restoration society"))
replace planning = 1 if (regexm(board, "review board"))
replace planning = 1 if (regexm(board, "home owner's "))
replace planning = 1 if (regexm(board, "view restoration "))
replace planning = 1 if (regexm(board, "assessment"))
replace planning = 1 if (regexm(board, "subdivision"))
replace planning = 1 if (regexm(board, "plan commission"))
replace planning = 1 if (regexm(board, "consolidation"))
 

gen poverty = 0
replace poverty = 1 if (regexm(board, "poor"))
replace poverty = 1 if (regexm(board, "charity"))
replace poverty = 1 if (regexm(board, "pauper"))
replace poverty = 1 if (regexm(board, "charities"))
replace poverty = 1 if (regexm(board, "poor"))
replace poverty = 1 if (regexm(board, "poor"))
replace poverty = 1 if (regexm(board, "poor"))
replace poverty = 1 if (regexm(board, "housing"))
replace poverty = 1 if (regexm(board, "rent"))
replace poverty = 1 if (regexm(board, "social service"))
replace poverty= 1 if (regexm(board, "social work"))
replace poverty = 1 if (regexm(board, "homeless")) 
replace poverty = 1 if (regexm(board, "shelter"))
replace poverty = 1 if (regexm(board, "anti-displacement"))
replace poverty = 1 if (regexm(board, "deferred assessment"))
replace poverty = 1 if (regexm(board, "grants in aid"))
replace poverty = 1 if (regexm(board, "commmunity service"))
replace poverty = 1 if (regexm(board, "welfare"))
replace poverty = 1 if (regexm(board, "landlord"))
replace poverty = 1 if (regexm(board, "tenement"))

gen taxes= 0 
replace taxes= 1 if (regexm(board, "tax"))
replace taxes= 1 if (regexm(board, "finance"))
replace taxes= 1 if (regexm(board, "compensation"))
replace taxes= 1 if (regexm(board, "appraisal"))
replace taxes= 1 if (regexm(board, "trusts"))
replace taxes= 1 if (regexm(board, "utilities"))
replace taxes= 1 if (regexm(board, "bond"))
replace taxes= 1 if (regexm(board, "audit"))
replace taxes= 1 if (regexm(board, "toll"))
replace taxes= 1 if (regexm(board, "levy"))
replace taxes= 1 if (regexm(board, "loan"))
replace taxes= 1 if (regexm(board, "loan"))
replace taxes= 1 if (regexm(board, "adjustment"))
replace taxes= 1 if (regexm(board, "budget"))
replace taxes= 1 if (regexm(board, "pension"))
replace taxes= 1 if (regexm(board, "contribution"))
replace taxes= 1 if (regexm(board, "bank"))
replace taxes= 1 if (regexm(board, "insurance"))
replace taxes= 1 if (regexm(board, "account"))
replace taxes= 1 if (regexm(board, "fund"))
replace taxes= 1 if (regexm(board, "beer"))
replace taxes= 1 if (regexm(board, "liquor"))
replace taxes= 1 if (regexm(board, "benefit"))
replace taxes= 1 if (regexm(board, "measure"))

replace taxes= 1 if (regexm(board, "contract compliance"))
replace taxes= 1 if (regexm(board, "endurance capital"))
replace taxes= 1 if (regexm(board, "financial review"))
replace taxes= 1 if (regexm(board, "municipal property"))
replace taxes= 1 if (regexm(board, "steering & rules"))
replace taxes= 1 if (regexm(board, "financ"))
replace taxes= 1 if (regexm(board, "greater colorado bureau"))
replace taxes= 1 if (regexm(board, "board of equalization"))

gen womens_issues = 0 
replace womens_issues= 1 if (regexm(board, "women")) 
replace womens_issues= 1 if (regexm(board, "woman"))
replace womens_issues= 1 if (regexm(board, "equal"))
replace womens_issues= 1 if (regexm(board, "family"))
replace womens_issues= 1 if (regexm(board, "gender"))
replace womens_issues= 1 if (regexm(board, "female"))



drop if board == "city of los angeles (appointed)" //these are political appointees to assist elected officials


save "$Data_clean/historical_dataset.dta", replace

